package org.example.mock;

import org.example.bean.*;

import java.math.BigDecimal;
import java.sql.*;
import java.sql.Date;
import java.time.*;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.ThreadLocalRandom;
import java.util.concurrent.atomic.AtomicLong;

public class OrderDataGenerator {
    // Database connection configuration
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/bookstore?useSSL=false&requireSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "000000";

    // Foreign key data
    private List<String> memberIds = new ArrayList<>();
    private List<String> storeIds = new ArrayList<>();
    private List<String> bookIds = new ArrayList<>();
    private List<String> employeeIds = new ArrayList<>();
    private List<String> existingOrderIds = new ArrayList<>();

    private Random random = new Random();
    private AtomicLong orderIdGenerator = new AtomicLong(1);
    private AtomicLong paymentIdGenerator = new AtomicLong(1);

    // Payment methods
    private static final int[] PAYMENT_METHODS = {1, 2, 3, 4}; // 1-现金 2-银行卡 3-移动支付 4-其他

    // Comments for orders
    private static final String[] COMMENTS = {
            "书籍包装完好，送货速度快",
            "内容很有价值，值得购买",
            "纸质不错，印刷清晰",
            "比书店便宜，性价比高",
            "快递员服务态度好",
            "书本有轻微折痕",
            "配送时间比预期长",
            "缺货等待时间太久",
            "书籍内容与描述不符",
            "整体满意，会再次购买"
    };

    // Order status mapping
    private static final Map<Integer, String> ORDER_STATUS_MAP = new HashMap<>();
    static {
        ORDER_STATUS_MAP.put(1, "已下单");
        ORDER_STATUS_MAP.put(2, "已支付");
        ORDER_STATUS_MAP.put(3, "已发货");
        ORDER_STATUS_MAP.put(4, "已完成");
        ORDER_STATUS_MAP.put(5, "已取消");
        ORDER_STATUS_MAP.put(6, "已退货");
    }

    public static void main(String[] args) {
        String startDateStr = "2025-06-01";
        String endDateStr = "2025-06-30";
        int numOrders = 500;

        try {
            OrderDataGenerator generator = new OrderDataGenerator();
            generator.generateData(startDateStr, endDateStr, numOrders);
            System.out.println("Bookstore order data generation completed!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void generateData(String startDateStr, String endDateStr, int numOrders) throws SQLException {
        // Parse dates
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        LocalDate startDate = LocalDate.parse(startDateStr, formatter);
        LocalDate endDate = LocalDate.parse(endDateStr, formatter);

        // Connect to database
        try (Connection conn = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD)) {
            // Fetch foreign keys
            fetchForeignKeys(conn);

            // Check if we have enough foreign key data
            if (memberIds.isEmpty() || storeIds.isEmpty() || bookIds.isEmpty()) {
                System.out.println("Error: Missing required foreign key data (members, stores, or books)");
                return;
            }

            // Start transaction
            conn.setAutoCommit(false);

            try (PreparedStatement salesStmt = conn.prepareStatement(
                    "INSERT INTO sales_record (order_id, store_id, book_id, sale_date, sale_time, " +
                            "quantity, unit_price, total_amount, payment_method, member_id, employee_id, create_time) " +
                            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

                 PreparedStatement paymentStmt = conn.prepareStatement(
                         "INSERT INTO payment (id, order_id, payment_date, payment_time, amount, " +
                                 "payment_method, status, member_id, store_id, create_time) " +
                                 "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) {

                int totalSales = 0;
                int totalPayments = 0;

                // Generate order data
                for (int i = 0; i < numOrders; i++) {
                    // Generate order data
                    String orderId = "ORD" + System.currentTimeMillis() + random.nextInt(1000);
                    LocalDate saleDate = randomDate(startDate, endDate);
                    LocalTime saleTime = LocalTime.of(9 + random.nextInt(10), random.nextInt(60)); // 9am-7pm

                    // Random book and quantity
                    String bookId = randomElement(bookIds);
                    int quantity = random.nextInt(3) + 1; // 1-3 copies

                    // Get book price
                    BigDecimal unitPrice = getBookPrice(conn, bookId);
                    BigDecimal totalAmount = unitPrice.multiply(BigDecimal.valueOf(quantity));

                    // Random payment method
                    int paymentMethod = PAYMENT_METHODS[random.nextInt(PAYMENT_METHODS.length)];

                    // Random member (70% chance) or guest purchase
                    String memberId = random.nextDouble() < 0.7 ? randomElement(memberIds) : null;

                    // Random employee
                    String employeeId = randomElement(employeeIds);

                    // Random order status
                    int status = random.nextInt(6) + 1; // 1-6

                    // Insert sales record
                    salesStmt.setString(1, orderId);
                    salesStmt.setString(2, randomElement(storeIds));
                    salesStmt.setString(3, bookId);
                    salesStmt.setDate(4, Date.valueOf(saleDate));
                    salesStmt.setTime(5, Time.valueOf(saleTime));
                    salesStmt.setInt(6, quantity);
                    salesStmt.setBigDecimal(7, unitPrice);
                    salesStmt.setBigDecimal(8, totalAmount);
                    salesStmt.setInt(9, paymentMethod);
                    salesStmt.setString(10, memberId);
                    salesStmt.setString(11, employeeId);
                    salesStmt.setTimestamp(12, Timestamp.valueOf(LocalDateTime.of(saleDate, saleTime)));
                    salesStmt.addBatch();

                    totalSales++;

                    // Generate payment record (only for paid orders)
                    if (status >= 2) { // Status 2+ means paid
                        long paymentId = paymentIdGenerator.getAndIncrement();

                        // Payment time is slightly after order time
                        LocalTime paymentTime = saleTime.plusMinutes(random.nextInt(30) + 1);

                        paymentStmt.setLong(1, paymentId);
                        paymentStmt.setString(2, orderId);
                        paymentStmt.setDate(3, Date.valueOf(saleDate));
                        paymentStmt.setTime(4, Time.valueOf(paymentTime));
                        paymentStmt.setBigDecimal(5, totalAmount);
                        paymentStmt.setInt(6, paymentMethod);
                        paymentStmt.setString(7, "Completed");
                        paymentStmt.setString(8, memberId);
                        paymentStmt.setString(9, randomElement(storeIds));
                        paymentStmt.setTimestamp(10, Timestamp.valueOf(LocalDateTime.of(saleDate, paymentTime)));
                        paymentStmt.addBatch();

                        totalPayments++;
                    }
                }

                // Execute batch inserts
                salesStmt.executeBatch();
                paymentStmt.executeBatch();

                // Commit transaction
                conn.commit();

                System.out.println("Successfully inserted " + totalSales + " sales records and " +
                        totalPayments + " payment records");
            } catch (SQLException e) {
                // Rollback transaction on error
                conn.rollback();
                throw e;
            }
        }
    }

    private void fetchForeignKeys(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            // Get member IDs
            try (ResultSet rs = stmt.executeQuery("SELECT member_id FROM member_info")) {
                while (rs.next()) {
                    memberIds.add(rs.getString("member_id"));
                }
            }

            // Get store IDs
            try (ResultSet rs = stmt.executeQuery("SELECT store_id FROM store_info")) {
                while (rs.next()) {
                    storeIds.add(rs.getString("store_id"));
                }
            }

            // Get book IDs
            try (ResultSet rs = stmt.executeQuery("SELECT book_id FROM book_info")) {
                while (rs.next()) {
                    bookIds.add(rs.getString("book_id"));
                }
            }

            // Generate employee IDs (since we don't have an employee table)
            for (int i = 0; i < 20; i++) {
                employeeIds.add("EMP" + String.format("%03d", i));
            }

            // Get existing order IDs (to avoid duplicates)
            try (ResultSet rs = stmt.executeQuery("SELECT order_id FROM sales_record")) {
                while (rs.next()) {
                    existingOrderIds.add(rs.getString("order_id"));
                }
            }
        }
    }

    private BigDecimal getBookPrice(Connection conn, String bookId) throws SQLException {
        String sql = "SELECT price FROM book_info WHERE book_id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, bookId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getBigDecimal("price");
                }
            }
        }
        return BigDecimal.valueOf(29.99); // Default price if not found
    }

    private LocalDate randomDate(LocalDate startDate, LocalDate endDate) {
        long startEpochDay = startDate.toEpochDay();
        long endEpochDay = endDate.toEpochDay();
        long randomDay = ThreadLocalRandom.current().nextLong(startEpochDay, endEpochDay);
        return LocalDate.ofEpochDay(randomDay);
    }

    private <T> T randomElement(List<T> list) {
        if (list.isEmpty()) {
            return null;
        }
        return list.get(random.nextInt(list.size()));
    }

    private String generateOrderComment(int rating) {
        if (rating < 3) { // Negative comment for low ratings
            return COMMENTS[5 + random.nextInt(4)]; // Index 5-8 are negative comments
        } else { // Positive comment for high ratings
            return COMMENTS[random.nextInt(5)]; // Index 0-4 are positive comments
        }
    }
}